﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using NuGet.Server.DataServices;
using System.Data;

namespace MayhemServer.Services {
    public class PackageDetailService : IPackageDetailService {
        private readonly IPackageService packageService;

        public PackageDetailService()
            : this(new PackageService()) {
        }

        public PackageDetailService(IPackageService packageService) {
            this.packageService = packageService;
        }

        public Package Increment(short packageDetailId) {
            using (var connection = DatabaseService.GetConnection()) {
                var findPackageDownload = new SqlCommand("SELECT PackageId, PackageVersion FROM mayhem_PackageDetail WHERE PackageDetailId = @PackageDetailId", connection);
                findPackageDownload.Parameters.AddWithValue("@PackageDetailId", packageDetailId);

                connection.Open();

                var reader = findPackageDownload.ExecuteReader();
                if (reader.Read()) {
                    var packageId = reader.GetString(reader.GetOrdinal("PackageId"));
                    var packageVersion = reader.GetString(reader.GetOrdinal("PackageVersion"));
                    reader.Close();

                    var updatePackageDownload = new SqlCommand("UPDATE mayhem_PackageDetail SET DownloadCount = DownloadCount + 1 WHERE PackageDetailId = @PackageDetailId", connection);
                    updatePackageDownload.Parameters.AddWithValue("@PackageDetailId", packageDetailId);
                    updatePackageDownload.ExecuteNonQuery();

                    return packageService.GetPackage(packageId, packageVersion);
                }
                else {
                    reader.Close();
                    return null;
                }
            }
        }

        public short GetPackageDetailId(Package package) {
            short packageDetailId;

            using (var connection = DatabaseService.GetConnection()) {
                var findPackageDownload = new SqlCommand("SELECT PackageDetailId FROM mayhem_PackageDetail WHERE PackageId = @PackageId AND PackageVersion = @PackageVersion", connection);
                findPackageDownload.Parameters.AddWithValue("@PackageId", package.Id);
                findPackageDownload.Parameters.AddWithValue("@PackageVersion", package.Version);

                connection.Open();

                var packageDetailIdValue = findPackageDownload.ExecuteScalar();
                if (packageDetailIdValue == null || packageDetailIdValue == DBNull.Value) {
                    var insertPackageDownload = new SqlCommand("INSERT INTO mayhem_PackageDetail (PackageId, PackageVersion, DownloadCount, IsLatestVersion) VALUES (@PackageId, @PackageVersion, 0, 0); SELECT CAST(SCOPE_IDENTITY() AS smallint)", connection);
                    insertPackageDownload.Parameters.AddWithValue("@PackageId", package.Id);
                    insertPackageDownload.Parameters.AddWithValue("@PackageVersion", package.Version);

                    var reader = insertPackageDownload.ExecuteReader();
                    reader.Read();
                    packageDetailId = (short)reader.GetInt16(0);
                    reader.Close();
                }
                else {
                    packageDetailId = (short)packageDetailIdValue;
                }
            }

            return packageDetailId;
        }

        public void FillDetails(Package package) {
            using (var connection = DatabaseService.GetConnection()) {
                var findIsLatestPackage = new SqlCommand("SELECT IsLatestVersion, DownloadCount FROM mayhem_PackageDetail WHERE PackageId = @PackageId AND PackageVersion = @PackageVersion", connection);
                findIsLatestPackage.Parameters.AddWithValue("@PackageId", package.Id);
                findIsLatestPackage.Parameters.AddWithValue("@PackageVersion", package.Version);

                connection.Open();

                bool isLatestVersion;
                int versionDownloadCount;
                var reader = findIsLatestPackage.ExecuteReader(CommandBehavior.SingleRow);
                if (reader.Read()) {
                    isLatestVersion = reader.GetBoolean(reader.GetOrdinal("IsLatestVersion"));
                    versionDownloadCount = reader.GetInt32(reader.GetOrdinal("DownloadCount"));
                    reader.Close();
                }
                else {
                    return;
                }

                var findPackageDetails = new SqlCommand("SELECT SUM(ISNULL(DownloadCount, 0)) FROM mayhem_PackageDetail WHERE PackageId = @PackageId GROUP BY PackageID", connection);
                findPackageDetails.Parameters.AddWithValue("@PackageId", package.Id);
                var downloadCount = (int)findPackageDetails.ExecuteScalar();

                package.DownloadCount = downloadCount;
                package.VersionDownloadCount = versionDownloadCount;
                package.IsLatestVersion = isLatestVersion;
            }
        }

        public string GetLatestVersion(string id) {
            using (var connection = DatabaseService.GetConnection()) {
                var findLatestVersion = new SqlCommand("SELECT PackageVersion FROM mayhem_PackageDetail WHERE PackageId = @PackageId AND IsLatestVersion = 1", connection);
                findLatestVersion.Parameters.AddWithValue("@PackageId", id);

                connection.Open();

                var packageVersionValue = findLatestVersion.ExecuteScalar();

                if (packageVersionValue == null || packageVersionValue == DBNull.Value) {
                    return null;
                }

                return (string)packageVersionValue;
            }
        }

        public void FillDetails(IEnumerable<Package> packages) {
            if (packages.Count() == 0) {
                return;
            }

            var downloadData = new Dictionary<string, int>();

            using (var connection = DatabaseService.GetConnection()) {
                var findPackageDownload = new SqlCommand(string.Format("SELECT PackageId, PackageVersion, DownloadCount AS DownloadCount FROM mayhem_PackageDetail WHERE PackageId IN ({0})", string.Join(", ", packages.Distinct().Select((p, i) => "@p" + i.ToString()).ToArray())), connection);
                var counter = 0;
                foreach (var package in packages) {
                    findPackageDownload.Parameters.AddWithValue("@p" + counter, package.Id);
                    counter++;
                }

                connection.Open();

                var reader = findPackageDownload.ExecuteReader();
                if (reader.HasRows) {
                    var packageIdIndex = reader.GetOrdinal("PackageId");
                    var packageVersionIndex = reader.GetOrdinal("PackageVersion");
                    var downloadCountIndex = reader.GetOrdinal("DownloadCount");

                    while (reader.Read()) {
                        downloadData.Add(string.Format("{0}.{1}", reader.GetString(packageIdIndex), reader.GetString(packageVersionIndex)), reader.GetInt32(downloadCountIndex));
                    }
                }
                reader.Close();

                foreach (var package in packages) {
                    var key = string.Format("{0}.{1}", package.Id, package.Version);
                    if (downloadData.ContainsKey(key)) {
                        package.VersionDownloadCount = downloadData[key];
                    }
                    else {
                        package.VersionDownloadCount = 0;

                        var insertPackageDownload = new SqlCommand("INSERT INTO mayhem_PackageDetail (PackageId, PackageVersion, DownloadCount, IsLatestVersion) VALUES (@PackageId, @PackageVersion, 0, 0)", connection);
                        insertPackageDownload.Parameters.AddWithValue("@PackageId", package.Id);
                        insertPackageDownload.Parameters.AddWithValue("@PackageVersion", package.Version);
                        insertPackageDownload.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}